package com.xsd.awen.utils;

import com.xsd.awen.config.ServiceException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DbUtils {

    private static Logger logger = LoggerFactory.getLogger(DbUtils.class);

    public static void disconnect(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Throwable e) {
            logger.warn(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    public static boolean executeSchema(Connection connection, String sql) {
        logger.info("insert sql {}",sql);
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.execute();
            return true;
        } catch (Exception e) {
            throw new ServiceException(e);
        } finally {
            closePs(statement);
        }
    }

    public static boolean executeUpdate(Connection connection, String sql) {
        return executeUpdate(connection,sql,null);
    }

    public static boolean executeUpdate(Connection connection, String sql,List<Object> params) {
        logger.info("insert sql {}",sql);
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    logger.info("insert param index {},value {}",i+1,params.get(i));
                    statement.setObject(i + 1,params.get(i));
                }
            }
            statement.executeUpdate();
            return true;
        } catch (Exception e) {
            logger.info("错误行数："+e.getStackTrace()[0].getLineNumber());
            throw new ServiceException(e);
        } finally {
            closePs(statement);
        }
    }

    public static List<Map<String, Object>> executeQuery(Connection conn, String sql) {
        return executeQuery(conn,sql,null);
    }

    public static List<Map<String, Object>> executeQuery(Connection conn, String sql,List<Object> params) {
        logger.info("query sql: {}",sql);
        List<Map<String, Object>> rows = Lists.newArrayList();
        ResultSet resultSet = null;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    logger.info("query param index {},value {}",i+1,params.get(i));
                    pstmt.setObject(i + 1,params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            List<String> columns = new ArrayList<>();
            for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
                columns.add(resultSet.getMetaData().getColumnName(i + 1));
            }
            while (resultSet.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (String column : columns) {
                    row.put(column, resultSet.getObject(column));
                }
                rows.add(row);
            }
            return rows;
        } catch (Exception e) {
            throw new ServiceException(e);
        } finally {
            closeRs(resultSet);
            closePs(pstmt);
        }
    }


    public static List<String> findColumns(Connection conn,List<Object> params){
        String sql = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        List<Map<String, Object>> data = executeQuery(conn,sql,params);

        List<String> cols = Lists.newArrayList();
        for( Map<String, Object> map : data ){
            cols.add(map.get("COLUMN_NAME").toString());
        }
        return cols;
    }

    public static void closeRs(ResultSet resultSet){
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                logger.warn(e.getMessage(), e);
            }
        }
    }

    public static void closePs(PreparedStatement ps){
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
                logger.warn(e.getMessage(), e);
            }
        }
    }

    public static void closeConn(Connection conn) {
        try {
            if( conn != null ){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}